// © 2009 Microsoft Corporation.  All rights reserved.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Validation;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.InformationSecurity.ISRM.TaskManagment.Classes;
using Microsoft.InformationSecurity.ISRM.TaskManagment.TaskMessages;
using System.Data;
using System.Data.SqlClient;
using System.Collections.ObjectModel;

namespace Microsoft.InformationSecurity.ISRM.TaskManagment.Classes
{
    /// <summary>
    /// Implements methods to manage notification schedules.
    /// </summary>
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1053:StaticHolderTypesShouldNotHaveConstructors")]
    public class Notifications
    {
        /// <summary>
        /// Adds a notification content to the database.
        /// </summary>
        /// <param name="content">New Notification Content</param>
        /// <returns>Identifiere of the newly created notification content</returns>
        public static int AddNotificationContent(NotificationContent content)
        {
            if (content == null)
                throw new ArgumentNullException("content");

            if (string.IsNullOrEmpty(content.Name))
                throw new ArgumentNullException("ContentName", TaskMessages.Messages.GetServiceMessage("ContentNameNotNull"));
            if (string.IsNullOrEmpty(content.Body))
                throw new ArgumentNullException("ContentBody", TaskMessages.Messages.GetServiceMessage("ContentBodyNotNull"));

            ValidationResults validationResults = Validation.Validate<NotificationContent>(content, "NotificationContentBasicRules");

            if (validationResults.IsValid == false)
            {
                throw new ArgumentException(TaskMessages.Messages.GetServiceMessage("ContentValidationFailed"));
            }
            int contentID = -1;
            Database db = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                SqlCommand createCatCommand = new SqlCommand("ISRM_spInsertNotificationContent");
                createCatCommand.CommandType = CommandType.StoredProcedure;
                createCatCommand.Parameters.Add("@name", SqlDbType.NVarChar, 100);
                createCatCommand.Parameters.Add("@body", SqlDbType.NVarChar);
                createCatCommand.Parameters.Add("@isBodyHtml", SqlDbType.NVarChar, 100);
                createCatCommand.Parameters.Add("@username", SqlDbType.NVarChar, 50);
                createCatCommand.Parameters.Add("@contentID", SqlDbType.Int);
                createCatCommand.Parameters["@name"].Value = content.Name;
                createCatCommand.Parameters["@body"].Value = content.Body;
                createCatCommand.Parameters["@isBodyHtml"].Value = content.IsHtml;
                createCatCommand.Parameters["@username"].Value = content.LastUpdatedBy;
                createCatCommand.Parameters["@contentID"].Direction = ParameterDirection.Output;
                createCatCommand.Parameters["@contentID"].Direction = ParameterDirection.Output;
                createCatCommand.Parameters.Add("@personID", SqlDbType.Int).Value = content.PersonID;

                db.ExecuteNonQuery(createCatCommand);
                contentID = (int)createCatCommand.Parameters["@contentID"].Value;
            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }
            return contentID;
        }

        /// <summary>
        /// Adds the notification to the processed notifications log table.
        /// </summary>
        /// <param name="notificationID">Notification ID</param>
        /// <param name="processedDate">Date when notification is processed</param>
        public static void AddProcesssedNotification(int notificationID,DateTime processedDate)
        {
           
            Database db = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                db.ExecuteNonQuery("ISRM_spInsertProcessedNotification",notificationID,processedDate);
            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }
        }

        /// <summary>
        /// Updates notification content in the database
        /// </summary>
        /// <param name="content">Existing Content</param>
        public static void UpdateNotificationContent(NotificationContent content)
        {
            if (content == null)
                throw new ArgumentNullException("content");

            if (string.IsNullOrEmpty(content.Name))
                throw new ArgumentNullException("ContentName", TaskMessages.Messages.GetServiceMessage("ContentNameNotNull"));
            if (string.IsNullOrEmpty(content.Body))
                throw new ArgumentNullException("ContentBody", TaskMessages.Messages.GetServiceMessage("ContentBodyNotNull"));

            ValidationResults validationResults = Validation.Validate<NotificationContent>(content, "NotificationContentBasicRules");

            if (validationResults.IsValid == false)
            {
                throw new ArgumentException(TaskMessages.Messages.GetServiceMessage("ContentValidationFailed"));
            }

            Database db = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                SqlCommand createCatCommand = new SqlCommand("ISRM_spUpdateNotificationContent");
                createCatCommand.CommandType = CommandType.StoredProcedure;
                createCatCommand.Parameters.Add("@name", SqlDbType.NVarChar, 100);
                createCatCommand.Parameters.Add("@body", SqlDbType.NVarChar);
                createCatCommand.Parameters.Add("@isBodyHtml", SqlDbType.NVarChar, 100);
                createCatCommand.Parameters.Add("@username", SqlDbType.NVarChar, 50);
                createCatCommand.Parameters.Add("@contentID", SqlDbType.Int);
                createCatCommand.Parameters["@contentID"].Value = content.ID;
                createCatCommand.Parameters["@name"].Value = content.Name;
                createCatCommand.Parameters["@body"].Value = content.Body;
                createCatCommand.Parameters["@isBodyHtml"].Value = content.IsHtml;
                createCatCommand.Parameters["@username"].Value = content.LastUpdatedBy;
                createCatCommand.Parameters.Add("@personID", SqlDbType.Int).Value = content.PersonID;

                db.ExecuteNonQuery(createCatCommand);

            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }

        }

        /// <summary>
        /// Deletes notification content from the database
        /// </summary>
        /// <param name="contentID">Content Identifier</param>
        /// <param name="personID">personID</param>
        public static void DeleteNotificationContent(int contentID, int personID)
        {
            if (contentID <= 0)
                throw new ArgumentNullException("contentID");


            Database db = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                db.ExecuteNonQuery("ISRM_spDeleteNotificationContent", contentID, personID);
            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }
        }

        /// <summary>
        /// Returns all notification contents in the database.
        /// </summary>
        /// <returns></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1002:DoNotExposeGenericLists")]
        public static List<NotificationContent> GetNotificationContents()
        {
            Database db = null; IDataReader dbReader = null;
            List<NotificationContent> lstContents = new List<NotificationContent>();
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = (SqlDataReader)db.ExecuteReader(CommandType.StoredProcedure, "ISRM_spGetNotificationContents");
                while (dbReader.Read())
                {
                    NotificationContent content = new NotificationContent();
                    content.ID = dbReader.GetInt32(0);
                    content.Name = dbReader.GetString(1);
                    content.Body = dbReader.GetString(2);
                    content.IsHtml = dbReader.GetBoolean(3);
                    content.LastUpdatedBy = dbReader.GetString(4);
                    content.LastUpdatedDate = dbReader.GetDateTime(5);
                    content.NotificationsCount = dbReader.GetInt32(6);
                    lstContents.Add(content);
                }
            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return lstContents;
        }

        /// <summary>
        /// Adds a notifications schedule and returns the identifier for the schedule.
        /// </summary>
        /// <param name="notificationSchedule">Notification schedule that needs to be added to the database</param>
        public static int AddNotificationSchedule(NotificationSchedule notificationSchedule)
        {
            if (notificationSchedule == null)
                throw new ArgumentNullException("NotificationSchedule");

            if (string.IsNullOrEmpty(notificationSchedule.Name))
                throw new ArgumentNullException("Schedule Name", TaskMessages.Messages.GetServiceMessage("ScheduleNameNotNull"));
            if (notificationSchedule.Notifications.Count == 0)
                throw new ArgumentNullException("Notifications Count", TaskMessages.Messages.GetServiceMessage("ScheduleNotificationsNotValid")); 

            ValidationResults validationResults = Validation.Validate<NotificationSchedule>(notificationSchedule, "NotificationScheduleBasicRules");

            if (validationResults.IsValid == false)
            {
                throw new ArgumentException(TaskMessages.Messages.GetServiceMessage("ScheduleValidationFailed"));
            }

            int scheduleID;
            Database db = DatabaseFactory.CreateDatabase("ISRMDB");
            SqlConnection conn = (SqlConnection)db.CreateConnection();
            SqlTransaction trans=null;

            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                SqlCommand createCatCommand = new SqlCommand("ISRM_spInsertNotificationSchedule",conn,trans);
                createCatCommand.CommandType = CommandType.StoredProcedure;
                createCatCommand.Parameters.Add("@name", SqlDbType.NVarChar, 100);
                createCatCommand.Parameters.Add("@userID", SqlDbType.Int);
                createCatCommand.Parameters.Add("@username", SqlDbType.NVarChar, 50);
                createCatCommand.Parameters.Add("@isDefault", SqlDbType.Bit);
                createCatCommand.Parameters.Add("@scheduleID", SqlDbType.Int);
                createCatCommand.Parameters["@name"].Value = notificationSchedule.Name;
                createCatCommand.Parameters["@userID"].Value = notificationSchedule.CreatedBy.ID;
                createCatCommand.Parameters["@username"].Value = notificationSchedule.LastUpdatedBy;
                createCatCommand.Parameters["@isDefault"].Value = notificationSchedule.IsDefault;
                createCatCommand.Parameters["@scheduleID"].Direction = ParameterDirection.Output;                

                createCatCommand.ExecuteNonQuery();
                
                scheduleID = (int)createCatCommand.Parameters["@scheduleID"].Value;

                foreach (Notification n in notificationSchedule.Notifications)
                {
                    SqlCommand createNotCommand = new SqlCommand("ISRM_spInsertOrUpdateNotification", conn,trans);
                    createNotCommand.CommandType = CommandType.StoredProcedure;
                    createNotCommand.Parameters.Add("@scheduleID", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@subject", SqlDbType.NVarChar, 255);
                    createNotCommand.Parameters.Add("@contentID", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@isRecurring", SqlDbType.Bit);
                    createNotCommand.Parameters.Add("@frequencyTypeID", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@frequency", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@dayOfWeek", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@dayOfMonth", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@isBeforeDueDate", SqlDbType.Bit);
                    createNotCommand.Parameters.Add("@occurances", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@notificationTypeID", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@username", SqlDbType.NVarChar, 50);
                    createNotCommand.Parameters.Add("@notificationID", SqlDbType.Int);
                    createNotCommand.Parameters["@scheduleID"].Value = scheduleID;
                    createNotCommand.Parameters["@subject"].Value = n.Subject;
                    createNotCommand.Parameters["@contentID"].Value = n.NotificationContent.ID;
                    createNotCommand.Parameters["@isRecurring"].Value = n.IsRecurring;
                    createNotCommand.Parameters["@frequencyTypeID"].Value = ((int)n.FrequencyType)+1;
                    createNotCommand.Parameters["@frequency"].Value = n.Frequency;
                    createNotCommand.Parameters["@dayOfWeek"].Value = n.DayOfWeek;
                    createNotCommand.Parameters["@dayOfMonth"].Value = n.DayOfMonth;
                    createNotCommand.Parameters["@isBeforeDueDate"].Value = n.IsBeforeDueDate;
                    createNotCommand.Parameters["@occurances"].Value = n.Occurances;
                    createNotCommand.Parameters["@notificationTypeID"].Value = 1;
                    createNotCommand.Parameters["@username"].Value = n.LastUpdatedBy;
                    createNotCommand.Parameters["@notificationID"].Direction = ParameterDirection.Output;

                    createNotCommand.ExecuteNonQuery();

                    n.ID = (int)createNotCommand.Parameters["@notificationID"].Value;

                    foreach (NotificationRecipient r in n.Recipients) 
                    {
                        SqlCommand createNotRecCommand = new SqlCommand("ISRM_spInsertOrUpdateNotificationRecipient", conn, trans);
                        createNotRecCommand.CommandType = CommandType.StoredProcedure;
                        createNotRecCommand.Parameters.Add("@notificationID", SqlDbType.Int);
                        createNotRecCommand.Parameters.Add("@recipientAddress", SqlDbType.NVarChar, 255);
                        createNotRecCommand.Parameters.Add("@recipientType", SqlDbType.Int);
                        createNotRecCommand.Parameters.Add("@username", SqlDbType.NVarChar, 50);
                        createNotRecCommand.Parameters.Add("@recipientID", SqlDbType.Int);
                        createNotRecCommand.Parameters["@notificationID"].Value = n.ID;
                        createNotRecCommand.Parameters["@recipientAddress"].Value = r.Address;
                        createNotRecCommand.Parameters["@recipientType"].Value = 1;
                        createNotRecCommand.Parameters["@username"].Value = r.LastUpdatedBy;
                        createNotRecCommand.Parameters["@recipientID"].Direction = ParameterDirection.Output;
                        createNotRecCommand.ExecuteNonQuery();
                    }
                }

                if (trans != null)
                    trans.Commit();
            }
            catch (SqlException sqlEx)
            {
                if (trans!=null)
                    trans.Rollback();

                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message.Substring(0,sqlEx.Message.IndexOf(Environment.NewLine)));
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn = null;
                }
                if (db != null)
                    db = null;
            }
            return scheduleID;
        }

        /// <summary>
        /// Updates a specific notification schedule
        /// </summary>
        public static void UpdateNotificationSchedule(NotificationSchedule notificationSchedule)
        {
            if (notificationSchedule == null)
                throw new ArgumentNullException("NotificationSchedule");

            if (string.IsNullOrEmpty(notificationSchedule.Name))
                throw new ArgumentNullException("Schedule Name", TaskMessages.Messages.GetServiceMessage("ScheduleNameNotNull"));
            if (notificationSchedule.Notifications.Count == 0)
                throw new ArgumentNullException("Notifications Count", TaskMessages.Messages.GetServiceMessage("ScheduleNotificationsNotValid"));

            ValidationResults validationResults = Validation.Validate<NotificationSchedule>(notificationSchedule, "NotificationScheduleBasicRules");

            if (validationResults.IsValid == false)
            {
                throw new ArgumentException(TaskMessages.Messages.GetServiceMessage("ScheduleValidationFailed"));
            }

            foreach (Notification n in notificationSchedule.Notifications) 
            {
                validationResults = Validation.Validate<Notification>(n, "NotificationBasicRules");
                if (!validationResults.IsValid)
                {
                    throw new ArgumentException("Notification Schedule contains invalid data.");
                }
            }

            Database db = DatabaseFactory.CreateDatabase("ISRMDB");
            SqlConnection conn = (SqlConnection)db.CreateConnection();
            SqlTransaction trans = null;

            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                SqlCommand createCatCommand = new SqlCommand("ISRM_spUpdateNotificationSchedule", conn, trans);
                createCatCommand.CommandType = CommandType.StoredProcedure;
                createCatCommand.Parameters.Add("@name", SqlDbType.NVarChar, 100);
                createCatCommand.Parameters.Add("@userID", SqlDbType.Int);
                createCatCommand.Parameters.Add("@username", SqlDbType.NVarChar, 50);
                createCatCommand.Parameters.Add("@isDefault", SqlDbType.Bit);
                createCatCommand.Parameters.Add("@scheduleID", SqlDbType.Int);
                createCatCommand.Parameters["@name"].Value = notificationSchedule.Name;
                createCatCommand.Parameters["@userID"].Value = notificationSchedule.CreatedBy.ID;
                createCatCommand.Parameters["@username"].Value = notificationSchedule.LastUpdatedBy;
                createCatCommand.Parameters["@isDefault"].Value = notificationSchedule.IsDefault;
                createCatCommand.Parameters["@scheduleID"].Value = notificationSchedule.ID;

                createCatCommand.ExecuteNonQuery();

                SqlCommand deleteNotifications = new SqlCommand("ISRM_spDeleteAllNotifications", conn, trans);
                deleteNotifications.CommandType = CommandType.StoredProcedure;
                deleteNotifications.Parameters.Add("@scheduleID", SqlDbType.Int);
                deleteNotifications.Parameters["@scheduleID"].Value = notificationSchedule.ID;
                deleteNotifications.ExecuteNonQuery();

                foreach (Notification n in notificationSchedule.Notifications)
                {
                    SqlCommand createNotCommand = new SqlCommand("ISRM_spInsertOrUpdateNotification", conn, trans);
                    createNotCommand.CommandType = CommandType.StoredProcedure;
                    createNotCommand.Parameters.Add("@scheduleID", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@subject", SqlDbType.NVarChar, 255);
                    createNotCommand.Parameters.Add("@contentID", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@isRecurring", SqlDbType.Bit);
                    createNotCommand.Parameters.Add("@frequencyTypeID", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@frequency", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@dayOfWeek", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@dayOfMonth", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@isBeforeDueDate", SqlDbType.Bit);
                    createNotCommand.Parameters.Add("@occurances", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@notificationTypeID", SqlDbType.Int);
                    createNotCommand.Parameters.Add("@username", SqlDbType.NVarChar, 50);
                    createNotCommand.Parameters.Add("@notificationID", SqlDbType.Int);
                    createNotCommand.Parameters["@scheduleID"].Value = notificationSchedule.ID;
                    createNotCommand.Parameters["@subject"].Value = n.Subject;
                    createNotCommand.Parameters["@contentID"].Value = n.NotificationContent.ID;
                    createNotCommand.Parameters["@isRecurring"].Value = n.IsRecurring;
                    createNotCommand.Parameters["@frequencyTypeID"].Value = ((int)n.FrequencyType) + 1;
                    createNotCommand.Parameters["@frequency"].Value = n.Frequency;
                    createNotCommand.Parameters["@dayOfWeek"].Value = n.DayOfWeek;
                    createNotCommand.Parameters["@dayOfMonth"].Value = n.DayOfMonth;
                    createNotCommand.Parameters["@isBeforeDueDate"].Value = n.IsBeforeDueDate;
                    createNotCommand.Parameters["@occurances"].Value = n.Occurances;
                    createNotCommand.Parameters["@notificationTypeID"].Value = 1;
                    createNotCommand.Parameters["@username"].Value = n.LastUpdatedBy;
                    createNotCommand.Parameters["@notificationID"].Direction = ParameterDirection.Output;

                    createNotCommand.ExecuteNonQuery();
                    n.ID = (int)createNotCommand.Parameters["@notificationID"].Value;
                    foreach (NotificationRecipient r in n.Recipients)
                    {
                        SqlCommand createNotRecCommand = new SqlCommand("ISRM_spInsertOrUpdateNotificationRecipient", conn, trans);
                        createNotRecCommand.CommandType = CommandType.StoredProcedure;
                        createNotRecCommand.Parameters.Add("@notificationID", SqlDbType.Int);
                        createNotRecCommand.Parameters.Add("@recipientAddress", SqlDbType.NVarChar, 255);
                        createNotRecCommand.Parameters.Add("@recipientType", SqlDbType.Int);
                        createNotRecCommand.Parameters.Add("@username", SqlDbType.NVarChar, 50);
                        createNotRecCommand.Parameters.Add("@recipientID", SqlDbType.Int);
                        createNotRecCommand.Parameters["@notificationID"].Value = n.ID;
                        createNotRecCommand.Parameters["@recipientAddress"].Value = r.Address;
                        createNotRecCommand.Parameters["@recipientType"].Value = 1;
                        createNotRecCommand.Parameters["@username"].Value = r.LastUpdatedBy;
                        createNotRecCommand.Parameters["@recipientID"].Value = r.ID;
                        createNotRecCommand.ExecuteNonQuery();
                    }
                }

                if (trans != null)
                    trans.Commit();
            }
            catch (SqlException sqlEx)
            {
                if (trans != null)
                    trans.Rollback();

                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message.Substring(0, sqlEx.Message.IndexOf(Environment.NewLine)));
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn = null;
                }
                if (db != null)
                    db = null;
            }
        }

        /// <summary>
        /// Deletes the specified notification schedule
        /// </summary>
        public static void DeleteNotificationSchedule(int notificationScheduleID, int personID)
        {
            if (notificationScheduleID <= 0)
                throw new ArgumentNullException("notificationScheduleID");


            Database db = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                db.ExecuteNonQuery("ISRM_spDeleteNotificationSchedule", notificationScheduleID, personID);
            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }
        }

        /// <summary>
        /// Updates the specified notification schedule as the defailt schedule
        /// </summary>
        public static void SetDefaultNotificationSchedule(int notificationScheduleID,string username, int personID)
        {
            if (notificationScheduleID <= 0)
                throw new ArgumentNullException("notificationScheduleID");


            Database db = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                db.ExecuteNonQuery("ISRM_spSetDefaultNotificationSchedule", notificationScheduleID, username, personID);
            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }
        }

        /// <summary>
        /// Returns the default notification schedule.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate")]
        public static int GetDefaultNotificationSchedule()
        {
            
            Database db = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                SqlCommand createNotRecCommand = new SqlCommand("ISRM_spGetDefaultNotificationSchedule");
                createNotRecCommand.CommandType = CommandType.StoredProcedure;
                object scheduleID = db.ExecuteScalar(createNotRecCommand);
                if (scheduleID != null)
                    return (int)scheduleID;
                else
                    return 0;
            }
            catch (SqlException sqlEx)
            {
                string dbErrorMessage = Messages.GetDatabaseMessage(sqlEx.Message);
                if (!string.IsNullOrEmpty(dbErrorMessage))
                {
                    throw new ArgumentException(dbErrorMessage);
                }
                else
                    throw;

            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }
        }
        

        /// <summary>
        /// Returns list of all notification schedules for a user
        /// </summary>
        
        public static ICollection<NotificationSchedule> GetNotificationSchedules(int userID)
        {
            Database db = null; IDataReader dbReader = null;
            List<NotificationSchedule> lstSchedules = new List<NotificationSchedule>();
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = (SqlDataReader)db.ExecuteReader("ISRM_spGetNotificationSchedulesByUser",userID);
                while (dbReader.Read())
                {
                    NotificationSchedule schedule = new NotificationSchedule();
                    schedule.ID = dbReader.GetInt32(0);
                    schedule.Name = dbReader.GetString(1);
                    schedule.IsDefault = dbReader.GetBoolean(2);
                    schedule.LastUpdatedBy = dbReader.GetString(3);
                    schedule.LastUpdatedDate = dbReader.GetDateTime(4);
                    schedule.CreatedBy = new User();
                    schedule.CreatedBy.ID = dbReader.GetInt32(5);
                    schedule.CreatedBy.Name = dbReader.GetString(6);
                    schedule.TaskCount = dbReader.GetInt32(7);

                    lstSchedules.Add(schedule);
                }
            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return lstSchedules;
        }

        /// <summary>
        /// Returns list of all notification schedules
        /// </summary>
        
        public static ICollection<NotificationSchedule> GetNotificationSchedules()
        {
            Database db = null;
            List<NotificationSchedule> lstSchedules = new List<NotificationSchedule>();
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                IDataReader dbReader = (SqlDataReader)db.ExecuteReader("ISRM_spGetNotificationSchedules");
                while (dbReader.Read())
                {
                    NotificationSchedule schedule = new NotificationSchedule();
                    schedule.ID = dbReader.GetInt32(0);
                    schedule.Name = dbReader.GetString(1);
                    schedule.IsDefault = dbReader.GetBoolean(2);
                    schedule.LastUpdatedBy = dbReader.GetString(3);
                    schedule.LastUpdatedDate = dbReader.GetDateTime(4);
                    schedule.CreatedBy = new User();
                    schedule.CreatedBy.ID = dbReader.GetInt32(5);
                    schedule.CreatedBy.Name = dbReader.GetString(6);
                    schedule.TaskCount = dbReader.GetInt32(7);

                    lstSchedules.Add(schedule);
                }
            }
            catch { throw; }
            finally
            {
                if (db != null)
                    db = null;
            }
            return lstSchedules;
        }

        /// <summary>
        /// Returns notification schedule along with the notifications inside the schedule.
        /// </summary>
        public static NotificationSchedule GetNotificationSchedule(int scheduleID)
        {
            NotificationSchedule schedule = null;
            Database db = null;
            IDataReader dbReader2 = null;
            IDataReader dbReader = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = (SqlDataReader)db.ExecuteReader("ISRM_spGetNotificationSchedule",scheduleID);
                while (dbReader.Read())
                {
                    schedule = new NotificationSchedule();
                    schedule.ID = dbReader.GetInt32(0);
                    schedule.Name = dbReader.GetString(1);
                    schedule.IsDefault = dbReader.GetBoolean(2);
                    schedule.LastUpdatedBy = dbReader.GetString(3);
                    schedule.LastUpdatedDate = dbReader.GetDateTime(4);
                    schedule.CreatedBy = new User();
                    schedule.CreatedBy.ID = dbReader.GetInt32(5);
                    schedule.CreatedBy.Name = dbReader.GetString(6);
                    schedule.TaskCount = dbReader.GetInt32(7);
                    schedule.Notifications = new Collection<Notification>();
                    dbReader2 = (SqlDataReader)db.ExecuteReader("ISRM_spGetNotifications", scheduleID);
                    while (dbReader2.Read())
                    {
                        Notification notification = new Notification();
                        notification.ID = dbReader2.GetInt32(0);
                        notification.Subject = dbReader2.GetString(1);
                        notification.NotificationContent = new NotificationContent();
                        notification.NotificationContent.ID = dbReader2.GetInt32(2);
                        notification.IsRecurring = dbReader2.GetBoolean(3);
                        notification.FrequencyType = (FrequencyType)(dbReader2.GetInt32(4)-1);
                        notification.Frequency = dbReader2.GetInt32(5);
                        notification.DayOfWeek = (DaysOfWeek)dbReader2.GetInt32(6);
                        notification.DayOfMonth = dbReader2.GetInt32(7);
                        notification.IsBeforeDueDate = dbReader2.GetBoolean(8);
                        notification.Occurances = dbReader2.GetInt32(9);
                        notification.Recipients = new Collection<NotificationRecipient>();
                        NotificationRecipient recipient= new NotificationRecipient();
                        recipient.ID = dbReader2.GetInt32(10);
                        recipient.Address = dbReader2.GetString(11);
                        recipient.Type = dbReader2.GetInt32(12);
                        recipient.LastUpdatedBy = dbReader2.GetString(13);
                        recipient.LastUpdatedDate = dbReader2.GetDateTime(14);
                        notification.Recipients.Add(recipient);
                        notification.LastUpdatedBy = dbReader2.GetString(15);
                        notification.LastUpdatedDate = dbReader2.GetDateTime(16);
                        schedule.Notifications.Add(notification);
                    }
                }
            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (dbReader2 != null)
                {
                    dbReader2.Close();
                    dbReader2 = null;
                }
                if (db != null)
                    db = null;
            }
            return schedule;
        }

        /// <summary>
        /// Returns notifications that are currently assigned to active or not started tasks.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1002:DoNotExposeGenericLists"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate")]
        public static List<Notification> GetAssignedNotifications()
        {
            List<Notification> notifications = new List<Notification>();
            Database db = null;
            IDataReader dbReader2 = null;
            try
            {

                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader2 = (SqlDataReader)db.ExecuteReader("ISRM_spGetAssignedNotifications");
                while (dbReader2.Read())
                {
                    Notification notification = new Notification();
                    notification.ID = dbReader2.GetInt32(0);
                    notification.Subject = dbReader2.GetString(1);
                    notification.NotificationContent = new NotificationContent();
                    notification.NotificationContent.ID = dbReader2.GetInt32(2);
                    notification.IsRecurring = dbReader2.GetBoolean(3);
                    notification.FrequencyType = (FrequencyType)(dbReader2.GetInt32(4) - 1);
                    notification.Frequency = dbReader2.GetInt32(5);
                    notification.DayOfWeek = (DaysOfWeek)dbReader2.GetInt32(6);
                    notification.DayOfMonth = dbReader2.GetInt32(7);
                    notification.IsBeforeDueDate = dbReader2.GetBoolean(8);
                    notification.Occurances = dbReader2.GetInt32(9);
                    notification.Recipients = new Collection<NotificationRecipient>();
                    NotificationRecipient recipient = new NotificationRecipient();
                    recipient.ID = dbReader2.GetInt32(10);
                    recipient.Address = dbReader2.GetString(11);
                    recipient.Type = dbReader2.GetInt32(12);
                    recipient.LastUpdatedBy = dbReader2.GetString(13);
                    recipient.LastUpdatedDate = dbReader2.GetDateTime(14);
                    notification.Recipients.Add(recipient);
                    notification.LastUpdatedBy = dbReader2.GetString(15);
                    notification.LastUpdatedDate = dbReader2.GetDateTime(16);
                    notification.LastProcessedDate = dbReader2.GetDateTime(17);
                    notifications.Add(notification);
                }
            }
            catch { throw; }
            finally
            {
                
                if (dbReader2 != null)
                {
                    dbReader2.Close();
                    dbReader2 = null;
                }
                if (db != null)
                    db = null;
            }
            return notifications;
        }


        /// <summary>
        /// Returns a list of users and their respective notifications.
        /// </summary>
        /// <param name="notificationIDs">String of comma separated notification IDs</param>
        /// <returns>List of users and notification ids</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1006:DoNotNestGenericTypesInMemberSignatures")]
        public static Dictionary<int, List<int>> GetNotificationsUsers(string notificationIDs) 
        {
            Dictionary<int, List<int>> dicUsersContents = new Dictionary<int, List<int>>();
            Database db = null;
            IDataReader dbReader2 = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader2 = (SqlDataReader)db.ExecuteReader("ISRM_spGetUniqueNotificationUsers",notificationIDs);
                while (dbReader2.Read())
                {
                    if (!dicUsersContents.ContainsKey(dbReader2.GetInt32(0)))
                    {
                        dicUsersContents.Add(dbReader2.GetInt32(0), new List<int>());
                    }
                    dicUsersContents[dbReader2.GetInt32(0)].Add(dbReader2.GetInt32(1));
                }
            }
            catch { throw; }
            finally
            {
                
                if (dbReader2 != null)
                {
                    dbReader2.Close();
                    dbReader2 = null;
                }
                if (db != null)
                    db = null;
            }
            return dicUsersContents;
        }

        /// <summary>
        /// Returns all notification contents for the specified notification ID.
        /// </summary>
        /// <returns></returns>
        
        public static ICollection<NotificationContent> GetUniqueNotificationContents(string notificationIDs)
        {
            Database db = null; IDataReader dbReader = null;
            List<NotificationContent> lstContents = new List<NotificationContent>();
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader = (SqlDataReader)db.ExecuteReader("ISRM_spGetUniqueNotificationContents", notificationIDs);
                while (dbReader.Read())
                {
                    NotificationContent content = new NotificationContent();
                    content.ID = dbReader.GetInt32(0);
                    content.Name = dbReader.GetString(1);
                    content.Body = dbReader.GetString(2);
                    content.IsHtml = dbReader.GetBoolean(3);
                    lstContents.Add(content);
                }
            }
            catch { throw; }
            finally
            {
                if (dbReader != null)
                {
                    dbReader.Close();
                    dbReader = null;
                }
                if (db != null)
                    db = null;
            }
            return lstContents;
        }

        /// <summary>
        /// Returns single notification for the specified notification ID.
        /// </summary>
        /// <param name="notificationID">Notification Identifier</param>
        /// <returns>Notification</returns>
        public static Notification GetNotification(int notificationID) 
        {
            Notification notification = new Notification();
            Database db = null;
            IDataReader dbReader2 = null;
            try
            {
                db = DatabaseFactory.CreateDatabase("ISRMDB");
                dbReader2 = (SqlDataReader)db.ExecuteReader("ISRM_spGetNotification", notificationID);
                while (dbReader2.Read())
                {
                    notification.ID = dbReader2.GetInt32(0);
                    notification.Subject = dbReader2.GetString(1);
                    notification.NotificationContent = new NotificationContent();
                    notification.NotificationContent.ID = dbReader2.GetInt32(2);
                    notification.IsRecurring = dbReader2.GetBoolean(3);
                    notification.FrequencyType = (FrequencyType)(dbReader2.GetInt32(4) - 1);
                    notification.Frequency = dbReader2.GetInt32(5);
                    notification.DayOfWeek = (DaysOfWeek)dbReader2.GetInt32(6);
                    notification.DayOfMonth = dbReader2.GetInt32(7);
                    notification.IsBeforeDueDate = dbReader2.GetBoolean(8);
                    notification.Occurances = dbReader2.GetInt32(9);
                    notification.Recipients = new Collection<NotificationRecipient>();
                    NotificationRecipient recipient = new NotificationRecipient();
                    recipient.ID = dbReader2.GetInt32(10);
                    recipient.Address = dbReader2.GetString(11);
                    recipient.Type = dbReader2.GetInt32(12);
                    recipient.LastUpdatedBy = dbReader2.GetString(13);
                    recipient.LastUpdatedDate = dbReader2.GetDateTime(14);
                    notification.Recipients.Add(recipient);
                    notification.LastUpdatedBy = dbReader2.GetString(15);
                    notification.LastUpdatedDate = dbReader2.GetDateTime(16);
                }
            }
            catch { throw; }
            finally
            {
                if (dbReader2 != null)
                {
                    dbReader2.Close();
                    dbReader2 = null;
                }
                if (db != null)
                    db = null;
            }
            return notification;
        }
    }
}
